import xlwings as xw
import re,json
import pandas as pd
import requests,json
import datetime
import decimal
import logging
def mylog(name='mylog'):
    # 创建一个Logger对象
    logger = logging.getLogger(name)
    logger.setLevel(logging.INFO)
    if not logger.handlers:
        # 创建一个文件处理器
        file_handler = logging.FileHandler('xl.log',encoding="utf-8")
        console_handler = logging.StreamHandler()
        # 创建一个日志格式化器
        formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
        file_handler.setFormatter(formatter)
        console_handler.setFormatter(formatter)
        # 将文件处理器添加到Logger中
        logger.addHandler(file_handler)
        logger.addHandler(console_handler)
    return logger
logger = mylog()

logger.info('启动或载入')

def ja_db(dc ,act='get',host=None):
    """ http读写数据库 
    """
    if host is None:
        host='7erpio.17121.top:2028'
    tpl = "http://{}/jsonapi/{}"
    url=tpl.format(host,act)
    data = json.dumps(dc,   default=datetime.datetime.isoformat)
    #--获取账号信息
    res_obj = requests.post(url ,data=data,timeout=25)#不能发送中文
    if res_obj.status_code != 200:
        logger.warning('访问 %s 服务错误 【%s】  / %s'%(url, res_obj.status_code, res_obj.text))
    #print(res_obj.text )
    dat_dc = json.loads(res_obj.text)
    return dat_dc 


def 重建一张表(tabName,wb):

    if tabName  in  [i.name for i in wb.sheets] :
        wb.sheets[ tabName ].delete()
    wb.sheets.add( tabName,after = wb.sheets['配置信息'])
    return wb.sheets[tabName]

def e读取销售对应入库单():
    wb = xw.Book.caller()
    tabName = '销售对应入库'
    #df2 = 读取销售对应入库单x计划跟踪号( )
    df2 = 读取销售对应入库单x需求单据( )
    #隐藏显示 字段
    #delCol = ['生产车间id', '创建人id', '物料id','FID', '创建人id','单据id','需求单据行号','明细行号']
    #df2 = df2.drop(columns=delCol)#不需要的列
    #移动位置
    # value_column = df2.pop('销售单日期')
    # df2.insert(0, '销售单日期', value_column)
    # value_column = df2.pop('需求单据号')
    # df2.insert(1, '需求单据号', value_column)
    # value_column = df2.pop('单据编号')
    # df2.insert(2, '单据编号', value_column)
    
    # value_column = df2.pop('明细id')
    #df2.insert(df2.shape[1], '明细id', value_column)
    showCol = "销售单日期	需求单据号	单据编号	源单编号	应收数量	实收数量	入库单日期	单据状态	焊充车间	物料代码	名称	型号	创建人	明细id"
    showCol = showCol.split('	')
    #---入库单
    tab=df2.loc[:, showCol] 
    sheet =  重建一张表(tabName,wb)
    sheet.range('A1').expand().options(pd.DataFrame, index=False).value = tab
    # 设置样式
    rowNum,rankNum = tab.shape

    s首行锁定(wb,sheet)
    s设置隔行背景(sheet)
    
    s设置格式类型(sheet,['销售单日期','入库单日期'],"yyyy-mm-dd")
    s设置格式类型(sheet,['型号'],"@")
    s设置单元格大小( sheet.range((1,1),(rowNum+1,rankNum+1))  )
    s设置单元格样式( sheet.range((1,1),(1,rankNum)) )
    
    s上下去重(sheet,2)
     
def 读取销售对应入库单x需求单据():
    """ 只显示一年内的销售订单  对应入库 
    需求单据一般都是有的  (手动投料单没有需求号)
    只包含持仓情况 加入备注
    select ta.FENTRYID,ta.FMTONO , tb.FREQBILLNO 
	from   T_PRD_INSTOCKENTRY ta
	left join  T_PRD_INSTOCKENTRY_A tb on  ta.FENTRYID = tb.FENTRYID
	where ta.FMTONO != tb.FREQBILLNO
    """
    """  更加贴合实际 """
    now = datetime.datetime.now()
    delta = datetime.timedelta(days=-180)
    n_days = now + delta
    
    col明细附加 = "FENTRYID.明细id,FREQBILLNO.需求单据号,FREQENTRYSEQ.需求单据行号"
    col明细 =  "FENTRYID.明细id,FID.单据id,FWORKSHOPID.生产车间id,FMATERIALID.物料id,FSRCBILLNO.源单编号,FSEQ.明细行号,FMUSTQTY.应收数量,FREALQTY.实收数量"
    #,FMTONO.计划跟踪号

    vn = {
    'erp/T_SAL_ORDER@订单':{#主表
        "FDOCUMENTSTATUS":"C",
        "FCLOSESTATUS":"A",
        "FCANCELSTATUS":"A",
        'FDATE&':">{}".format(n_days.strftime('%Y-%m-%d')),
        #"FBILLNO":"2402-003",
        #"@limit":1
        "@column": " FID,FBILLNO.需求单据号,FDATE.销售单日期",
    },
    #----需求单据 条件数据
    "erp/T_PRD_INSTOCKENTRY_A@生产入库明细附加9":{
        "@column":col明细附加,
        'FREQBILLNO|':"erp/T_SAL_ORDER@订单.需求单据号"
    },
    'erp/T_PRD_INSTOCKENTRY@生产入库明细9':{  
        "@column":col明细,
        'FENTRYID|':"erp/T_PRD_INSTOCKENTRY_A@生产入库明细附加9.明细id"
    } ,
 
    #---其他
    "erp/T_PRD_INSTOCK@生产入库单":{
        "@column":"FID.单据id,FBILLNO.单据编号,FCREATORID.创建人id,FDATE.入库单日期,FDOCUMENTSTATUS.单据状态",
        'FID|':"erp/T_PRD_INSTOCKENTRY@生产入库明细9.单据id"
    },
    'erp/T_BD_DEPARTMENT_L@部门':{
        "@column":"FDEPTID.生产车间id,FNAME.焊充车间",
        "FDEPTID|": "erp/T_PRD_INSTOCKENTRY@生产入库明细9.生产车间id",
        'FLOCALEID': 2052,
    }, 
    "erp/T_BD_MATERIAL@物料": {
        'FMATERIALID|':"erp/T_PRD_INSTOCKENTRY@生产入库明细9.物料id",
        "@column": "FMATERIALID.物料id,FNUMBER.物料代码",
    },
    'erp/T_BD_MATERIAL_L@物料信息':{
        "@column": "FMATERIALID.物料id,FNAME.名称,F_QINL_SPECIFICATION.型号",#FSPECIFICATIONGG.规格,
        'FMATERIALID|':"erp/T_BD_MATERIAL@物料.物料id",
        'FLOCALEID': 2052,
    } ,
    "erp/T_SEC_USER@用户": {
        "@column": "FUSERID.创建人id,FNAME.创建人",
        "FUSERID|": "erp/T_PRD_INSTOCK@生产入库单.创建人id"
    },
    }

    vv = ja_db( vn) 
    df订单 = pd.DataFrame( vv['erp/T_SAL_ORDER@订单']) #主线
    #df订单明细 = pd.DataFrame( vv['erp/T_SAL_ORDERENTRY@订单明细'])
    df生产入库明细9 = pd.DataFrame( vv['erp/T_PRD_INSTOCKENTRY@生产入库明细9'])
    df生产入库明细附加9 = pd.DataFrame( vv['erp/T_PRD_INSTOCKENTRY_A@生产入库明细附加9']) 

    
    #其他df
    df生产入库单 = pd.DataFrame( vv['erp/T_PRD_INSTOCK@生产入库单'])
    df部门 = pd.DataFrame( vv['erp/T_BD_DEPARTMENT_L@部门'])
    df物料 = pd.DataFrame( vv['erp/T_BD_MATERIAL@物料'])
    df物料信息 = pd.DataFrame( vv['erp/T_BD_MATERIAL_L@物料信息'])
    df用户 = pd.DataFrame( vv['erp/T_SEC_USER@用户'])
    #---join 
    df2 = pd.merge(df订单,df生产入库明细附加9,how="left",on="需求单据号") 
    df2 = pd.merge(df2,df生产入库明细9,how="left",on="明细id")
    df2 = pd.merge(df2,df生产入库单,how="left",on="单据id")
    df2 = pd.merge(df2,df部门,how="left",on="生产车间id")
    df2 = pd.merge(df2,df物料,how="left",on="物料id")
    df2 = pd.merge(df2,df物料信息,how="left",on="物料id")
    df2 = pd.merge(df2,df用户,how="left",on="创建人id")

    #--- apply不缺
    #df订单x = pd.merge(df订单,df订单明细,how="left",on="FID") 
    #df订单x = df订单x.groupby(['计划跟踪号']).first()
    #df2 = pd.merge(df2,df订单x,how="left",on="计划跟踪号")


    df2.sort_values(by=['销售单日期','需求单据号'], inplace=True ,ascending=False)
    return df2
 

def e读取下单的生产订单():
    """     """
    wb = xw.Book.caller()
    tabName = '下单的生产订单'
    df2 = 读取下单的生产订单( )
    #隐藏显示 字段
    delCol = ['FSALEORDERID', 'FSALEORDERENTRYID', '物料id','FID', '用户id','结案类型']
    df2 = df2.drop(columns=delCol)#不需要的列
    #移动位置
    value_column = df2.pop('单据日期')
    df2.insert(0, '单据日期', value_column)
    value_column = df2.pop('生产单据')
    df2.insert(1, '生产单据', value_column)
    value_column = df2.pop('明细id')
    df2.insert(df2.shape[1], '明细id', value_column)
    #-----生产订单
    tab=df2
    sheet =  重建一张表(tabName,wb)
    sheet.range('A1').expand().options(pd.DataFrame, index=False).value = tab
    # 设置样式
    rowNum,rankNum = tab.shape
    sheet.range((2,1),(rowNum+1,1)).api.NumberFormat = "yyyy-mm-dd"  #日期格式
    s设置单元格大小( sheet.range((1,1),(rowNum+1,rankNum+1))  )
    s设置单元格样式( sheet.range((1,1),(1,rankNum)) )
    s设置隔行背景(sheet)
    s首行锁定(wb,sheet)
    s上下去重(sheet,2)
     
def 读取下单的生产订单( ):
    """ data-attdata   """
    vn = {
    'erp/T_PRD_MO@生产订单':{
        #"FBILLNO":FBILLNO,
        "@column": 'FDATE.单据日期,FID,FBILLNO.生产单据' ,
        'FID|': 'erp/T_PRD_MOENTRY_A@生产订单明细附加.FID',
        #"@limit":1
    } ,
    'erp/T_PRD_MOENTRY@生产明细':{
        "@column": 'FENTRYID.明细id,FSEQ.明细行,FSALEORDERID,FSALEORDERENTRYID,FSALEORDERNO.需求单号,FSALEORDERENTRYSEQ.需求行号,FSRCBILLNO.计划编号,FMATERIALID.物料id,FQTY.数量' ,
        #'FID|': 'erp/T_PRD_MOENTRY_A@生产订单明细附加.FID', 
        'FENTRYID|': 'erp/T_PRD_MOENTRY_A@生产订单明细附加.明细id', #二级引用
    }, 
    "erp/T_PRD_MOENTRY_A@生产订单明细附加":{ #主
        "@column": "FID,FENTRYID.明细id,FSTATUS.业务状态,FSTOCKINQUAQTY.入库数量",
        #'FID|': 'erp/T_PRD_MO@生产订单.FID',
        "FSTATUS#":[ 3,4,5], # 业务状态
    },
    'erp/T_PRD_MOENTRY_Q@生产订单明细数量':{
        "@column":  "FENTRYID.明细id,FFORCECLOSERID.用户id,FCLOSETYPE.结案类型,FPICKMTLQTY.领料套数,FPICKMTRLSTATUS.领料状态" ,
        #'FID|': 'erp/T_PRD_MOENTRY_A@生产订单明细附加.FID',
        'FENTRYID|': 'erp/T_PRD_MOENTRY_A@生产订单明细附加.明细id', #二级引用
    },
    "erp/T_BD_MATERIAL_L@物料明细": {
        "FMATERIALID|": "erp/T_PRD_MOENTRY@生产明细.物料id" ,
        "@column": "FMATERIALID.物料id,FNAME.名称,FSPECIFICATIONGG.规格,F_QINL_SPECIFICATION.型号",
    } ,
    "erp/T_BD_MATERIAL@物料": {
        "FMATERIALID|": "erp/T_PRD_MOENTRY@生产明细.物料id" ,
        "@column": "FMATERIALID.物料id,FNUMBER.物料代码",
    },
    
    }
    vv = ja_db(vn)
    df生产订单明细附加 = pd.DataFrame( vv['erp/T_PRD_MOENTRY_A@生产订单明细附加']) #主线
    df生产明细 = pd.DataFrame( vv['erp/T_PRD_MOENTRY@生产明细'])
    df生产订单明细数量 = pd.DataFrame( vv['erp/T_PRD_MOENTRY_Q@生产订单明细数量'])
    df生产订单 = pd.DataFrame(vv['erp/T_PRD_MO@生产订单'])
    df物料明细 = pd.DataFrame(vv['erp/T_BD_MATERIAL_L@物料明细'])
    df物料 = pd.DataFrame(vv['erp/T_BD_MATERIAL@物料'])
    #---
    df2 = pd.merge(df生产订单明细附加,df生产明细,how="left",on="明细id")
    df2 = pd.merge(df2,df生产订单明细数量,how="left",on="明细id")
    df2 = pd.merge(df2,df生产订单,how="left",on="FID")
    df2 = pd.merge(df2,df物料明细,how="left",on="物料id")
    df2 = pd.merge(df2,df物料,how="left",on="物料id")
    # if df用户.shape[0] > 0:
    #     df2 = pd.merge(df2,df用户,how="left",on="用户id")
    #转换
    业务状态 = {'1':'计划' ,'2':'计划确认','3':'下达','4':'开工','5':'完工','6':'结案','7':'结算'}
    领料状态 ={'1':'未领料','2':'部分领料','3':'全部领料','4':'超额领料'}
    df2['业务状态'] = df2['业务状态'].map(业务状态)
    df2['领料状态'] = df2['领料状态'].map(领料状态)
    df2.sort_values(by=['单据日期','生产单据'], inplace=True ,ascending=False)
    return df2




def e读取销售进行中的订单():
    """     """
    wb = xw.Book.caller()
    tabName = '销售进行中的订单'
    df2 = 读取销售进行中的订单( )
    #隐藏显示 字段
    delCol = [ '物料id','仓库id','FID', '客户id','单据类型id','销售员id' ]
    df2 = df2.drop(columns=delCol)#不需要的列
    # #移动位置
    value_column = df2.pop('单据日期')
    df2.insert(0, '单据日期', value_column)
    value_column = df2.pop('单据编号')
    df2.insert(1, '单据编号', value_column)
    value_column = df2.pop('明细id')
    df2.insert(df2.shape[1], '明细id', value_column)
    #-----售进行中的订单
    tab=df2
    sheet =  重建一张表(tabName,wb)
    sheet.range('A1').expand().options(pd.DataFrame, index=False).value = tab
    # 设置样式
    rowNum,rankNum = tab.shape

    s设置格式类型(sheet,['单据日期','计划发货日期'],"yyyy-mm-dd")
    s设置格式类型(sheet,['型号'],"@")
    s设置单元格大小( sheet.range((1,1),(rowNum+1,rankNum+1))  )
    s设置单元格样式( sheet.range((1,1),(1,rankNum)) )
    s设置隔行背景(sheet)
    s首行锁定(wb,sheet)
    s上下去重(sheet,2)


def s设置格式类型(sheet,colName,format= "yyyy-mm-dd"):
    rowNum = sheet.used_range.last_cell.row
    rankNum = sheet.used_range.last_cell.column
    cols = sheet.range((1,1),(1,rankNum)).value
    for col in colName:
        idx = cols.index(col)
        if idx >= 0:
            sheet.range((1,idx+1),( rowNum,idx+1)).api.NumberFormat =format  #日期格式

def 读取销售进行中的订单( ):
    """   """
    vn = {
    'erp/T_SAL_ORDER@订单':{#主表
        "FDOCUMENTSTATUS":"C",
        "FCLOSESTATUS":"A",
        "FCANCELSTATUS":"A",
        #"FBILLNO":"2402-003",
        #"@limit":1
        "@column": " FID,FDATE.单据日期,FBILLNO.单据编号,FCUSTID.客户id,FBILLTYPEID.单据类型id,FSALERID.销售员id",
    },
    'erp/T_SAL_ORDERENTRY@订单明细':{
        "FID|": "erp/T_SAL_ORDER@订单.FID",
        "@column": " FID,FENTRYID.明细id,FMATERIALID.物料id,FQTY.数量,FPLANDELIVERYDATE.计划发货日期,FMTONO.计划跟踪号",
    },
    'erp/T_SAL_ORDERENTRY_R@订单明细关联':{
        "@column":"FENTRYID.明细id,FDELIQTY.累计发货通知数量,FSTOCKOUTQTY.累计出库数量",
       "FENTRYID|":"erp/T_SAL_ORDERENTRY@订单明细.明细id",
    }  ,
    # "erp/T_SAL_ORDERENTRY_D@订单交货":{
    #      "@column":"FENTRYID.明细id,FDELIVERYDATE.要货日期",
    #      "FENTRYID|":"erp/T_SAL_ORDERENTRY@订单明细.明细id",
    # },
    "erp/T_BD_MATERIAL@物料": {
        "FMATERIALID|": "erp/T_SAL_ORDERENTRY@订单明细.物料id" ,
        "@column": "FMATERIALID.物料id,FNUMBER.物料代码",
    },
    'erp/T_BD_MATERIAL_L@物料信息':{
        "@column": "FMATERIALID.物料id,FNAME.名称,F_QINL_SPECIFICATION.型号",#FSPECIFICATIONGG.规格,
        'FMATERIALID|':"erp/T_SAL_ORDERENTRY@订单明细.物料id",
        'FLOCALEID': 2052,
    } ,
    'erp/T_BD_CUSTOMER_L@客户':{ 
        "@column": " FCUSTID.客户id, FNAME.客户名称 ",
        "FCUSTID|":"erp/T_SAL_ORDER@订单.客户id",
        'FLOCALEID': 2052,
    },
    'erp/T_STK_INVENTORY@即时库存':{#T_STK_INVENTORY
        "@column":"FMATERIALID.物料id,FBASEQTY.库存数量,FSTOCKID.仓库id",
        'FBASEQTY&':">0",
        'FMATERIALID|':"erp/T_SAL_ORDERENTRY@订单明细.物料id",
    } ,
    "erp/T_BD_STOCK_L@仓库":{
        "@column": "FSTOCKID.仓库id,FNAME.仓库",
        "FSTOCKID|":"erp/T_STK_INVENTORY@即时库存.仓库id",
        'FLOCALEID': 2052,
    },
    'erp/T_BAS_BILLTYPE_L@单据类型':{
         "@column": " FBILLTYPEID.单据类型id,FNAME.单据类型",
        "FBILLTYPEID|":"erp/T_SAL_ORDER@订单.单据类型id",
         'FLOCALEID': 2052,
    },
    "erp/T_BD_OPERATORENTRY_L@销售员": {
        "@column": "FENTRYID.销售员id,FNAME.销售员",
        "FENTRYID|": "erp/T_SAL_ORDER@订单.销售员id"
    },
    }
    vv = ja_db(vn)
    df订单 = pd.DataFrame( vv['erp/T_SAL_ORDER@订单']) #主线
    df单据类型 = pd.DataFrame(vv['erp/T_BAS_BILLTYPE_L@单据类型'])
    df销售员 = pd.DataFrame(vv['erp/T_BD_OPERATORENTRY_L@销售员'])
    df订单明细 = pd.DataFrame( vv['erp/T_SAL_ORDERENTRY@订单明细'])
    df订单明细关联 = pd.DataFrame( vv['erp/T_SAL_ORDERENTRY_R@订单明细关联'])
    
    #df订单交货 = pd.DataFrame( vv['erp/T_SAL_ORDERENTRY_D@订单交货'])
    
    df物料 = pd.DataFrame(vv['erp/T_BD_MATERIAL@物料'])
    df物料信息 = pd.DataFrame(vv['erp/T_BD_MATERIAL_L@物料信息'])
    df客户 = pd.DataFrame( vv['erp/T_BD_CUSTOMER_L@客户'])
    df即时库存 = pd.DataFrame(vv['erp/T_STK_INVENTORY@即时库存'])
    df仓库 = pd.DataFrame(vv['erp/T_BD_STOCK_L@仓库'])
    
    
    
    
    #---
    df2 = pd.merge(df订单,df单据类型,how="left",on="单据类型id")
    df2 = pd.merge(df2,df订单明细,how="left",on="FID")
    df2 = pd.merge(df2,df订单明细关联,how="left",on="明细id")
    #df2 = pd.merge(df2,df订单交货,how="left",on="明细id")
    df2 = pd.merge(df2,df客户,how="left",on="客户id")
    df2 = pd.merge(df2,df物料,how="left",on="物料id")
    df2 = pd.merge(df2,df物料信息,how="left",on="物料id")
    df2 = pd.merge(df2,df销售员,how="left",on="销售员id")
    
    
    if df即时库存.shape[0] > 0:#无库存情况  
        # 按物料聚合
        df3 = pd.merge(df即时库存,df仓库,how="left",on="仓库id")
        gb4 = df3.groupby(['物料id'])
        df4 = gb4.sum()
        df5 = gb4.agg( 仓库=("仓库",  lambda x:  ','.join(set(x)) ),)
        #--库存，仓库
        df2 = pd.merge(df2,df4,how='left',left_on='物料id',right_index=True) 
        df2 = pd.merge(df2,df5,how='left',left_on='物料id',right_index=True) 
         

    df2.sort_values(by=['单据日期','单据编号'], inplace=True ,ascending=False)
    return df2

def s首行锁定(wb,sheet):
    wb.app.api.ActiveWindow.FreezePanes = False
    sheet.range((2,1)).select()#可活动的单元格
    wb.app.api.ActiveWindow.FreezePanes = True

 
 
def s设置单元格大小(cell):
    
    cell.columns.autofit()
    

def s设置单元格样式(cell):
    """ 可以批量 """
    cell.api.HorizontalAlignment = -4108
    cell.api.Font.Bold = True 

def s设置隔行背景(sheet):
    rowNum = sheet.used_range.last_cell.row
    rankNum = sheet.used_range.last_cell.column
    colorNum = (250,240,230)
    for i  in range(1,rowNum+1)  :
        if (i % 2)==0 :
            sheet.range((i,1),(i,rankNum) ).color = colorNum
    
def s上下去重(sheet,col=1):
    rowNum = sheet.used_range.last_cell.row
    cellVal = ''
    for i in range(1,rowNum+1):
        cell =  sheet.range((i,col) )
        if cellVal == cell.value:
            #sheet.range((i,col) ).clear()# 改成与背景同色
            cell.api.Font.Color = cell.api.Interior.Color #背景同色
        else:
            cellVal =  cell.value
 


if __name__ == "__main__":
    xw.Book("myproject.xlsm").set_mock_caller()
    
